package com.lyouu.dataminer.dao;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.lyouu.dataminer.db.DbConstants;
import com.lyouu.dataminer.factory.MysqlDbFactory;
import com.lyouu.dataminer.util.Constants;
import com.lyouu.dataminer.util.TourMap;

public class MdSubjectBustourDao {
	public int insert(int sid, String content, String departCity,
			String routine, String currency, String price, String provider,
			String booking, String childPrice, String groupPrice,
			String tourArea, String travelDate, String departCountry,
			String highlights) throws SQLException {
		Connection conn = MysqlDbFactory.getConnection(DbConstants.DB_LYOUU);
		String sql = "INSERT INTO md_subject_bustour"
				+ "(sid,forumid,templateid,content,c_departcity,c_routine,c_provider,c_booking,c_tourarea,c_traveldate,c_departcountry,c_highlights,c_priceunit,c_price,c_childprice,c_5groupprice,c_destcountry)"
				+ " VALUES (?,'0','0',?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setInt(1, sid);
		stmt.setString(2, content);
		stmt.setString(3, departCity);
		stmt.setString(4, routine);
		stmt.setString(5, provider);
		stmt.setString(6, booking);
		stmt.setString(7, tourArea);
		stmt.setString(8, travelDate);
		stmt.setString(9, departCountry);
		stmt.setString(10, highlights);
		stmt.setString(11, currency);
		if (!price.equals(Constants.UNKNOWN_PRICE))
			stmt.setBigDecimal(12, BigDecimal.valueOf(Double.valueOf(price)));
		else
			stmt.setBigDecimal(12, BigDecimal.valueOf(0));
		if (!childPrice.equals(Constants.UNKNOWN_PRICE))
			stmt.setBigDecimal(13,
					BigDecimal.valueOf(Double.valueOf(childPrice)));
		else
			stmt.setBigDecimal(13, BigDecimal.valueOf(0));
		if (!groupPrice.equals(Constants.UNKNOWN_PRICE))
			stmt.setBigDecimal(14,
					BigDecimal.valueOf(Double.valueOf(groupPrice)));
		else
			stmt.setBigDecimal(14, BigDecimal.valueOf(0));
		stmt.setString(15, TourMap.getInstance().findDestCountries(routine));
		return stmt.executeUpdate();
	}

	public void updateDestCountry(int sid, String destCountry)
			throws SQLException {
		Connection conn = MysqlDbFactory.getConnection(DbConstants.DB_LYOUU);
		String sql = "UPDATE md_subject_bustour SET c_destcountry=? WHERE sid=?";
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setString(1, destCountry);
		stmt.setInt(2, sid);
		stmt.executeUpdate();
		conn.close();
	}

	public int getSidByOrigPageUrl(String pageUrl) throws SQLException {
		int sid = Constants.BUS_TOUR_DEFAULT_SID;
		Connection conn = MysqlDbFactory.getConnection(DbConstants.DB_LYOUU);
		String sql = "SELECT sid FROM md_subject_bustour WHERE c_booking=?";
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setString(1, pageUrl);
		ResultSet rs = stmt.executeQuery();
		while (rs.next()) {
			sid = rs.getInt(1);
		}
		return sid;
	}
}
